# -*- coding: utf-8 -*-
"""
数据模型类 - 各模块的CRUD操作
"""

from database import Database
import datetime
import sqlite3

class IPManagementModel:
    """IP管理数据模型"""
    
    def __init__(self):
        self.db = Database()
    
    def add_ip(self, ip_address, device_type, department, purpose, asset_id=None, responsible_person=None, network_segment=None, status='启用', remark=None):
        """添加IP记录"""
        if self.db.check_ip_exists(ip_address):
            raise ValueError(f"IP地址 {ip_address} 已存在")
        
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            INSERT INTO ip_management (ip_address, device_type, department, purpose, asset_id, responsible_person, network_segment, status, remark)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (ip_address, device_type, department, purpose, asset_id, responsible_person, network_segment, status, remark))
        conn.commit()
        conn.close()
        self.db.log_operation('添加IP', ip_address, f'IP: {ip_address}, 设备类型: {device_type}')
    
    def update_ip(self, id, ip_address, device_type, department, purpose, asset_id=None, responsible_person=None, network_segment=None, status=None, remark=None):
        """更新IP记录"""
        if self.db.check_ip_exists(ip_address, exclude_id=id):
            raise ValueError(f"IP地址 {ip_address} 已存在")
        
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            UPDATE ip_management 
            SET ip_address=?, device_type=?, department=?, purpose=?, asset_id=?, responsible_person=?, network_segment=?, status=?, remark=?, updated_time=?
            WHERE id=?
        ''', (ip_address, device_type, department, purpose, asset_id, responsible_person, network_segment, status, remark, datetime.datetime.now(), id))
        conn.commit()
        conn.close()
        self.db.log_operation('修改IP', ip_address, f'IP: {ip_address}, 设备类型: {device_type}')
    
    def delete_ip(self, id):
        """删除IP记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT ip_address FROM ip_management WHERE id=?', (id,))
        ip_address = cursor.fetchone()[0]
        cursor.execute('DELETE FROM ip_management WHERE id=?', (id,))
        conn.commit()
        conn.close()
        self.db.log_operation('删除IP', ip_address, f'删除IP: {ip_address}')
    
    def get_all_ips(self):
        """获取所有IP记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM ip_management ORDER BY network_segment, ip_address')
        records = cursor.fetchall()
        conn.close()
        return [dict(record) for record in records]
    
    def get_ips_by_segment(self, segment):
        """根据网段获取IP记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM ip_management WHERE network_segment = ? ORDER BY ip_address', (segment,))
        records = cursor.fetchall()
        conn.close()
        return [dict(record) for record in records]
    
    def get_ips_by_status(self, status):
        """根据状态获取IP记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM ip_management WHERE status = ? ORDER BY network_segment, ip_address', (status,))
        records = cursor.fetchall()
        conn.close()
        return [dict(record) for record in records]
    
    def get_ips_by_segment_and_status(self, segment, status):
        """根据网段和状态获取IP记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM ip_management WHERE network_segment = ? AND status = ? ORDER BY ip_address', (segment, status))
        records = cursor.fetchall()
        conn.close()
        return [dict(record) for record in records]
    
    def search_ips(self, keyword):
        """搜索IP记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            SELECT * FROM ip_management 
            WHERE ip_address LIKE ? OR device_type LIKE ? OR department LIKE ? OR purpose LIKE ? OR remark LIKE ?
            ORDER BY network_segment, ip_address
        ''', (f'%{keyword}%', f'%{keyword}%', f'%{keyword}%', f'%{keyword}%', f'%{keyword}%'))
        records = cursor.fetchall()
        conn.close()
        return [dict(record) for record in records]
    
    def search_ips_in_segment(self, segment, keyword):
        """在指定网段中搜索IP记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            SELECT * FROM ip_management 
            WHERE network_segment = ? AND (ip_address LIKE ? OR device_type LIKE ? OR department LIKE ? OR purpose LIKE ? OR remark LIKE ?)
            ORDER BY ip_address
        ''', (segment, f'%{keyword}%', f'%{keyword}%', f'%{keyword}%', f'%{keyword}%', f'%{keyword}%'))
        records = cursor.fetchall()
        conn.close()
        return [dict(record) for record in records]

class AssetInfoModel:
    """资产信息数据模型"""
    
    def __init__(self):
        self.db = Database()
    
    def add_asset(self, asset_number, device_type, model=None, purchase_date=None, cpu_cores=None, memory_gb=None, disk_gb=None, virtualization_type=None):
        """添加资产记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        try:
            cursor.execute('''
                INSERT INTO asset_info (asset_number, device_type, model, purchase_date, cpu_cores, memory_gb, disk_gb, virtualization_type)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ''', (asset_number, device_type, model, purchase_date, cpu_cores, memory_gb, disk_gb, virtualization_type))
            conn.commit()
            self.db.log_operation('添加资产', asset_number, f'资产编号: {asset_number}, 设备类型: {device_type}')
        except sqlite3.IntegrityError:
            raise ValueError(f"资产编号 {asset_number} 已存在")
        finally:
            conn.close()
    
    def update_asset(self, id, asset_number, device_type, model=None, purchase_date=None, cpu_cores=None, memory_gb=None, disk_gb=None, virtualization_type=None):
        """更新资产记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            UPDATE asset_info 
            SET asset_number=?, device_type=?, model=?, purchase_date=?, cpu_cores=?, memory_gb=?, disk_gb=?, virtualization_type=?, updated_time=?
            WHERE id=?
        ''', (asset_number, device_type, model, purchase_date, cpu_cores, memory_gb, disk_gb, virtualization_type, datetime.datetime.now(), id))
        conn.commit()
        conn.close()
        self.db.log_operation('修改资产', asset_number, f'资产编号: {asset_number}, 设备类型: {device_type}')
    
    def delete_asset(self, id):
        """删除资产记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT asset_number FROM asset_info WHERE id=?', (id,))
        asset_number = cursor.fetchone()[0]
        cursor.execute('DELETE FROM asset_info WHERE id=?', (id,))
        conn.commit()
        conn.close()
        self.db.log_operation('删除资产', asset_number, f'删除资产: {asset_number}')
    
    def get_all_assets(self):
        """获取所有资产记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM asset_info ORDER BY asset_number')
        records = cursor.fetchall()
        conn.close()
        return [dict(record) for record in records]
    
    def search_assets(self, keyword):
        """搜索资产记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            SELECT * FROM asset_info 
            WHERE asset_number LIKE ? OR device_type LIKE ? OR model LIKE ?
            ORDER BY asset_number
        ''', (f'%{keyword}%', f'%{keyword}%', f'%{keyword}%'))
        records = cursor.fetchall()
        conn.close()
        return [dict(record) for record in records]

class MiddlewareInfoModel:
    """中间件信息数据模型"""
    
    def __init__(self):
        self.db = Database()
    
    def add_middleware(self, name, type, version=None, install_directory=None, port=None, associated_ip=None, associated_system=None):
        """添加中间件记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            INSERT INTO middleware_info (name, type, version, install_directory, port, associated_ip, associated_system)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', (name, type, version, install_directory, port, associated_ip, associated_system))
        conn.commit()
        conn.close()
        self.db.log_operation('添加中间件', name, f'中间件: {name}, 类型: {type}')
    
    def update_middleware(self, id, name, type, version=None, install_directory=None, port=None, associated_ip=None, associated_system=None):
        """更新中间件记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            UPDATE middleware_info 
            SET name=?, type=?, version=?, install_directory=?, port=?, associated_ip=?, associated_system=?, updated_time=?
            WHERE id=?
        ''', (name, type, version, install_directory, port, associated_ip, associated_system, datetime.datetime.now(), id))
        conn.commit()
        conn.close()
        self.db.log_operation('修改中间件', name, f'中间件: {name}, 类型: {type}')
    
    def delete_middleware(self, id):
        """删除中间件记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT name FROM middleware_info WHERE id=?', (id,))
        name = cursor.fetchone()[0]
        cursor.execute('DELETE FROM middleware_info WHERE id=?', (id,))
        conn.commit()
        conn.close()
        self.db.log_operation('删除中间件', name, f'删除中间件: {name}')
    
    def get_all_middleware(self):
        """获取所有中间件记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM middleware_info ORDER BY name')
        records = cursor.fetchall()
        conn.close()
        return [dict(record) for record in records]
    
    def search_middleware(self, keyword):
        """搜索中间件记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            SELECT * FROM middleware_info 
            WHERE name LIKE ? OR type LIKE ? OR version LIKE ?
            ORDER BY name
        ''', (f'%{keyword}%', f'%{keyword}%', f'%{keyword}%'))
        records = cursor.fetchall()
        conn.close()
        return [dict(record) for record in records]

class AppSystemModel:
    """应用系统数据模型"""
    
    def __init__(self):
        self.db = Database()
    
    def add_app_system(self, system_name, login_url=None, admin_account=None, admin_password=None, associated_ip=None, system_type=None):
        """添加应用系统记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            INSERT INTO app_system (system_name, login_url, admin_account, admin_password, associated_ip, system_type)
            VALUES (?, ?, ?, ?, ?, ?)
        ''', (system_name, login_url, admin_account, admin_password, associated_ip, system_type))
        conn.commit()
        conn.close()
        self.db.log_operation('添加应用系统', system_name, f'系统: {system_name}')
    
    def update_app_system(self, id, system_name, login_url=None, admin_account=None, admin_password=None, associated_ip=None, system_type=None):
        """更新应用系统记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            UPDATE app_system 
            SET system_name=?, login_url=?, admin_account=?, admin_password=?, associated_ip=?, system_type=?, updated_time=?
            WHERE id=?
        ''', (system_name, login_url, admin_account, admin_password, associated_ip, system_type, datetime.datetime.now(), id))
        conn.commit()
        conn.close()
        self.db.log_operation('修改应用系统', system_name, f'系统: {system_name}')
    
    def delete_app_system(self, id):
        """删除应用系统记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT system_name FROM app_system WHERE id=?', (id,))
        system_name = cursor.fetchone()[0]
        cursor.execute('DELETE FROM app_system WHERE id=?', (id,))
        conn.commit()
        conn.close()
        self.db.log_operation('删除应用系统', system_name, f'删除系统: {system_name}')
    
    def get_all_app_systems(self):
        """获取所有应用系统记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM app_system ORDER BY system_name')
        records = cursor.fetchall()
        conn.close()
        return [dict(record) for record in records]
    
    def search_app_systems(self, keyword):
        """搜索应用系统记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            SELECT * FROM app_system 
            WHERE system_name LIKE ? OR login_url LIKE ? OR admin_account LIKE ?
            ORDER BY system_name
        ''', (f'%{keyword}%', f'%{keyword}%', f'%{keyword}%'))
        records = cursor.fetchall()
        conn.close()
        return [dict(record) for record in records]

class OSAccountModel:
    """操作系统账号数据模型"""
    
    def __init__(self):
        self.db = Database()
    
    def add_os_account(self, account_name, account_level, account_type, account_password=None, associated_ip=None):
        """添加操作系统账号记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            INSERT INTO os_accounts (account_name, account_level, account_type, account_password, associated_ip)
            VALUES (?, ?, ?, ?, ?)
        ''', (account_name, account_level, account_type, account_password, associated_ip))
        conn.commit()
        conn.close()
        self.db.log_operation('添加系统账号', account_name, f'账号: {account_name}, 级别: {account_level}')
    
    def update_os_account(self, id, account_name, account_level, account_type, account_password=None, associated_ip=None):
        """更新操作系统账号记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            UPDATE os_accounts 
            SET account_name=?, account_level=?, account_type=?, account_password=?, associated_ip=?, updated_time=?
            WHERE id=?
        ''', (account_name, account_level, account_type, account_password, associated_ip, datetime.datetime.now(), id))
        conn.commit()
        conn.close()
        self.db.log_operation('修改系统账号', account_name, f'账号: {account_name}, 级别: {account_level}')
    
    def delete_os_account(self, id):
        """删除操作系统账号记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT account_name FROM os_accounts WHERE id=?', (id,))
        account_name = cursor.fetchone()[0]
        cursor.execute('DELETE FROM os_accounts WHERE id=?', (id,))
        conn.commit()
        conn.close()
        self.db.log_operation('删除系统账号', account_name, f'删除账号: {account_name}')
    
    def get_all_os_accounts(self):
        """获取所有操作系统账号记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM os_accounts ORDER BY account_name')
        records = cursor.fetchall()
        conn.close()
        return [dict(record) for record in records]
    
    def search_os_accounts(self, keyword):
        """搜索操作系统账号记录"""
        conn = self.db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            SELECT * FROM os_accounts 
            WHERE account_name LIKE ? OR account_level LIKE ? OR account_type LIKE ?
            ORDER BY account_name
        ''', (f'%{keyword}%', f'%{keyword}%', f'%{keyword}%'))
        records = cursor.fetchall()
        conn.close()
        return [dict(record) for record in records]